﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;

public class DataLayer
{
    private static SqlConnection con;

    public static SqlConnection Connection
    {
        get { return DataLayer.con; }
        set { DataLayer.con = value; }
    }

    private static SqlCommand cmd;

    #region SqlHelper Class
    private static Hashtable paramCache = Hashtable.Synchronized(new Hashtable());
    private static SqlParameter[] CloneParameters(SqlParameter[] originalParameters)
    {
        SqlParameter[] clonedParameters = new SqlParameter[originalParameters.Length];

        for (int i = 0, j = originalParameters.Length; i < j; i++)
        {
            clonedParameters[i] = (SqlParameter)((ICloneable)originalParameters[i]).Clone();
        }

        return clonedParameters;
    }
    private static SqlParameter[] GetSpParameterSetInternal(SqlConnection connection, string spName, bool includeReturnValueParameter)
    {
        if (connection == null) throw new ArgumentNullException("connection");
        if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

        string hashKey = connection.ConnectionString + ":" + spName + (includeReturnValueParameter ? ":include ReturnValue Parameter" : "");

        SqlParameter[] cachedParameters;

        cachedParameters = paramCache[hashKey] as SqlParameter[];
        if (cachedParameters == null)
        {
            SqlParameter[] spParameters = DiscoverSpParameterSet(connection, spName, includeReturnValueParameter);
            paramCache[hashKey] = spParameters;
            cachedParameters = spParameters;
        }

        return CloneParameters(cachedParameters);
    }
    private static SqlParameter[] DiscoverSpParameterSet(SqlConnection connection, string spName, bool includeReturnValueParameter)
    {
        if (connection == null) throw new ArgumentNullException("connection");
        if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName");

        SqlCommand cmd = new SqlCommand(spName, connection);
        cmd.CommandType = CommandType.StoredProcedure;

        connection.Open();
        SqlCommandBuilder.DeriveParameters(cmd);
        connection.Close();

        if (!includeReturnValueParameter)
        {
            cmd.Parameters.RemoveAt(0);
        }

        SqlParameter[] discoveredParameters = new SqlParameter[cmd.Parameters.Count];

        cmd.Parameters.CopyTo(discoveredParameters, 0);

        // Init the parameters with a DBNull value
        foreach (SqlParameter discoveredParameter in discoveredParameters)
        {
            discoveredParameter.Value = DBNull.Value;
        }
        return discoveredParameters;
    }

    private static void AssignParameterValues(SqlParameter[] commandParameters, object[] parameterValues)
    {
        if ((commandParameters == null) || (parameterValues == null))
        {
            // Do nothing if we get no data
            return;
        }

        // We must have the same number of values as we pave parameters to put them in
        if (commandParameters.Length != parameterValues.Length)
        {
            throw new ArgumentException("Parameter count does not match Parameter Value count.");
        }

        // Iterate through the SqlParameters, assigning the values from the corresponding position in the 
        // value array
        for (int i = 0, j = commandParameters.Length; i < j; i++)
        {
            // If the current array value derives from IDbDataParameter, then assign its Value property
            if (parameterValues[i] is IDbDataParameter)
            {
                IDbDataParameter paramInstance = (IDbDataParameter)parameterValues[i];
                if (paramInstance.Value == null)
                {
                    commandParameters[i].Value = DBNull.Value;
                }
                else
                {
                    commandParameters[i].Value = paramInstance.Value;
                }
            }
            else if (parameterValues[i] == null)
            {
                commandParameters[i].Value = DBNull.Value;
                cmd.Parameters.Add(commandParameters[i]);
            }
            else
            {
                commandParameters[i].Value = parameterValues[i];
                cmd.Parameters.Add(commandParameters[i]);
            }
        }
    }

    protected static SqlParameter[] GetSpParameterSet(SqlConnection connection, string spName, bool includeReturnValueParameter)
    {
        if (connection == null) throw new ArgumentNullException("connection");
        using (SqlConnection clonedConnection = (SqlConnection)((ICloneable)connection).Clone())
        {
            return GetSpParameterSetInternal(clonedConnection, spName, includeReturnValueParameter);
        }
    }

    #endregion

    public static void PrepareCommand(string spName, bool includeReturnValue, params object[] parameterValues)
    {
        cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = spName;
        SqlParameter[] arr_param = GetSpParameterSet(con, spName, includeReturnValue);

        if (arr_param.Length != parameterValues.Length)
        {
            ArrayList list = new ArrayList();
            list.Add(null);

            foreach (object paramItem in parameterValues)
                list.Add(paramItem);

            parameterValues = list.ToArray();
        }

        AssignParameterValues(arr_param, parameterValues);
    }

    public static object GetReturnValue()
    {
        return cmd.Parameters["@RETURN_VALUE"].Value;
    }

    public static void ConnectionClose()
    {
        con.Close();
    }

    public static object ExecuteScalar()
    {
        con.Open();
        object ret = cmd.ExecuteScalar();
        con.Close();
        return ret;
    }

    public static SqlDataReader ExecuteReader()
    {
        con.Open();
        SqlDataReader ret = cmd.ExecuteReader(CommandBehavior.CloseConnection);
        return ret;
    }

    public static DataSet ExecuteDataSet()
    {
        SqlDataAdapter da = new SqlDataAdapter();
        DataSet ds = new DataSet();
        da.SelectCommand = cmd;
        da.Fill(ds);
        return ds;
    }

    public static SqlDataAdapter ExecuteDataAdapter()
    {
        SqlDataAdapter da = new SqlDataAdapter();
        da.SelectCommand = cmd;
        return da;
    }

    public static int ExecuteNonQuery()
    {
        con.Open();
        int ret = cmd.ExecuteNonQuery();
        con.Close();
        return ret;
    }
}